import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class ZoneManager {
	public static final String ZONE_TABLE = "ZONE";
	public static final String LINE_STATION_TABLE = "LINE_STATION";
	public static final String ZONE_ID_COLUMN = "zone_id";
	public static final String NAME_COLUMN = "name";
	
	private static ZoneManager instance = null;
	public static ZoneManager getInstance() throws SQLException {
		if (instance == null) {
			instance = new ZoneManager();
			return instance;
		} else {
			return instance;
		}
	}
	protected ZoneManager() throws SQLException {
		conn = Database.getAccessDBConnection();
	}
	Connection conn = null;
	
	public Zone getZone(int zoneId) {
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + ZONE_TABLE 
					+ " WHERE "+ ZONE_ID_COLUMN + "=" + zoneId);
			ResultSet rs = stmt.getResultSet();
			if (!rs.next()) {
				stmt.close();
				return null;
			} else {
				Zone z = new Zone(rs);
				stmt.close();
				return z;
			}
		} catch (SQLException e) {
			return null;
		}
	}
	
	public ArrayList<Zone> getAllZone() {
		ArrayList<Zone> zones = new ArrayList<Zone>();
		try {
			Statement stmt = conn.createStatement();
			stmt.execute("SELECT * FROM " + ZONE_TABLE);
			ResultSet rs = stmt.getResultSet();
			while (rs.next()) {
				Zone z = new Zone(rs);
				zones.add(z);
			}
			stmt.close();
		} catch (Exception e) {
			e.getCause();
		}
		return zones;
	}
	
	public boolean addZone(Zone zone) {
		try {
			Statement stmt = conn.createStatement();
			String sql = "INSERT INTO " + ZONE_TABLE + "(" + NAME_COLUMN + ")"
					+ " VALUES " + "('" + zone.getName() + "')"; 
			stmt.executeUpdate(sql);
			stmt.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean updateZone(int zoneId, Zone newData) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE " + ZONE_TABLE + " SET name='" + newData.getName() + "' WHERE " + ZONE_ID_COLUMN + "=" + zoneId);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean removeZone(int id) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM " + ZONE_TABLE + " WHERE " + ZONE_ID_COLUMN + "=" + id);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
}
